CREATE TABLE [dbo].[Configuration](
	[Id] [int] IDENTITY(1,1) PRIMARY KEY,
	[Alias] [nvarchar](255)UNIQUE NOT NULL ,
	[Name] [nvarchar](255) NOT NULL ,
	[Val] [nvarchar](255) NOT NULL ,
	[Detail] [nvarchar](4000)  DEFAULT '',
	[Created_Date][datetime]  DEFAULT GETDATE(),
	[flag] [nvarchar](10)  DEFAULT '',
 )
GO
CREATE TABLE [dbo].[Group](
	[Id] [int] IDENTITY(1,1)  PRIMARY KEY,
	[Name] [nvarchar](255) ,
	[Alias] [nvarchar](255) UNIQUE NOT NULL ,
	[Detail] [nvarchar](4000)  DEFAULT '',	
	[Created_Date][datetime]  DEFAULT GETDATE(),
	[flag] [nvarchar](10)  DEFAULT '',
	)
GO
CREATE TABLE [dbo].[GroupMember](
	[Id] [int] IDENTITY(1,1)PRIMARY KEY,
	[GroupId] [int] ,
	[UserId] [int] ,	
	[Created_Date][datetime]  DEFAULT GETDATE(),
	[flag] [nvarchar](10)  DEFAULT '',
)
GO
CREATE TABLE [dbo].[Menu](
	[Id] [int] IDENTITY(1,1)PRIMARY KEY,
	[PId] [int]  DEFAULT 0,
	[Ord] [int]  DEFAULT 0,
	[Name] [nvarchar](50) NOT NULL,
	[DisplayName] [nvarchar](50) NOT NULL ,
	[Link] [nvarchar](255)  DEFAULT '',
	[ModuleId] [int]  DEFAULT 0,
	[Access] [int]  DEFAULT 0,
	[Created_Date][datetime]  DEFAULT GETDATE(),
	[flag] [nvarchar](10)  DEFAULT '',
)
GO
CREATE TABLE [dbo].[Module](
	[Id] [int] IDENTITY(1,1)  PRIMARY KEY,
	[Name] [nvarchar](255) NOT NULL,
	[Alias] [nvarchar](255) UNIQUE NOT NULL,
	[Detail] [nvarchar](4000)  DEFAULT '',	
	[Created_Date][datetime]  DEFAULT GETDATE(),
	[flag] [nvarchar](10)  DEFAULT '',
)
GO

CREATE TABLE [dbo].[Policy](
	[Id] [int] IDENTITY(1,1) PRIMARY KEY,
	[GroupId] [int] ,
	[ModuleId] [int] ,
	[Val] [nvarchar](255) DEFAULT '',	
	[View] [bit] NOT NULL default 0,	
	[Insert] [bit] NOT NULL default 0,
	[Delete] [bit] NOT NULL default 0,
	[Update] [bit] NOT NULL default 0,
	[All] [bit] NOT NULL default 0,
	[Created_Date][datetime]  DEFAULT GETDATE(),
	[flag] [nvarchar](10)  DEFAULT '',
	
)
GO

CREATE TABLE [dbo].[User](
	[Id] [int] IDENTITY(1,1) PRIMARY KEY,
	[OpenId] [nvarchar](255)  DEFAULT '',
	[Status] [nvarchar](255)  DEFAULT '',
	[LoginName] [nvarchar](255)  DEFAULT '',
	[Email] [nvarchar](255) UNIQUE NOT NULL DEFAULT '',
	[Avatar] [nvarchar](255)  DEFAULT '',
	[Birthday] [datetime]  DEFAULT GETDATE(),
	[Sex] [nvarchar](50)  DEFAULT 'Nam',
	[FullName] [nvarchar](255)  DEFAULT '',
	[Password] [nvarchar](255)  DEFAULT '',
	[Address] [nvarchar](255)  DEFAULT '',
	[Phone] [nvarchar](255)  DEFAULT '',
	[Mobile] [nvarchar](255)  DEFAULT '',
	[Yahoo] [nvarchar](255)  DEFAULT '',
	[Skype] [nvarchar](255)  DEFAULT '',
	[Facebook] [nvarchar](255)  DEFAULT '',
	[Detail] [nvarchar](4000)  DEFAULT '',
	[Skin] [nvarchar](255)  DEFAULT '',
	[Css] [nvarchar](4000)  DEFAULT '',
	[JustLogin] [datetime]  DEFAULT GETDATE(),
	[LastLogin] [datetime]  DEFAULT GETDATE(),
	[Created_Date][datetime]  DEFAULT GETDATE(),
	[flag] [nvarchar](10)  DEFAULT '',
)
GO


--unique
ALTER TABLE [dbo].[Policy] ADD CONSTRAINT UQ_Policy_Unique UNIQUE([GroupId], [ModuleId])
GO
ALTER TABLE [dbo].[GroupMember]  ADD CONSTRAINT UQ_GroupMember_Unique UNIQUE([GroupId], [UserId]) 
GO
--foreign key

ALTER TABLE [dbo].[GroupMember]  WITH CHECK ADD  
CONSTRAINT [FK_GroupMember_Group] FOREIGN KEY([GroupId])REFERENCES [dbo].[Group] ([Id]) on delete cascade on update cascade,
CONSTRAINT [FK_GroupMember_User] FOREIGN KEY([UserId])REFERENCES [dbo].[User] ([Id]) on delete cascade on update cascade
GO

ALTER TABLE [dbo].[Policy]  WITH CHECK ADD  
CONSTRAINT [FK_Policy_Group] FOREIGN KEY([GroupId])REFERENCES [dbo].[Group] ([Id])on delete cascade on update cascade,
CONSTRAINT [FK_Policy_Module] FOREIGN KEY([ModuleId])REFERENCES [dbo].[Module] ([Id])on delete cascade on update cascade
GO

ALTER TABLE [dbo].[Menu]  WITH CHECK ADD  
CONSTRAINT [FK_Menu_Module] FOREIGN KEY([ModuleId])REFERENCES [dbo].[Module] ([Id])on delete cascade on update cascade
GO


-- TRIGGER


CREATE TRIGGER Mudul_must_policy ON [Module]
FOR INSERT, UPDATE 
AS

BEGIN TRY
		
		BEGIN TRANSACTION Mudul_must_policy;
		
				DECLARE @GroupId int, @ModuleIdInserted int
				select @ModuleIdInserted = Id from inserted

				DECLARE Mudul_must_policy_cursor CURSOR FOR 
				SELECT [Id]
				FROM [Group]
				OPEN Mudul_must_policy_cursor
				FETCH NEXT FROM Mudul_must_policy_cursor 
				INTO @GroupId

				WHILE @@FETCH_STATUS = 0
				BEGIN
					IF NOT EXISTS (	   SELECT *
							   FROM [Policy] a INNER JOIN [Module] b ON a.ModuleId= b.Id    
							   WHERE  a.[GroupId] =  @GroupId AND a.ModuleId =  @ModuleIdInserted    
						  )
					 INSERT INTO [Policy]([GroupId],[ModuleId],[Val],[Created_Date],flag) values(@GroupId,@ModuleIdInserted,'',getdate(),'')
					FETCH NEXT FROM Mudul_must_policy_cursor INTO @GroupId

				END 
				CLOSE Mudul_must_policy_cursor;
				DEALLOCATE Mudul_must_policy_cursor;
		COMMIT TRANSACTION Mudul_must_policy;
 		END TRY
		BEGIN CATCH
			PRINT 'rollback Mudul_must_policy';
			ROLLBACK TRAN Mudul_must_policy;	
		END CATCH

GO


CREATE TRIGGER Group_must_policy ON [Group]
FOR INSERT, UPDATE 
AS

BEGIN TRY
		
		BEGIN TRANSACTION Group_must_policy;
		
				DECLARE @ModuleId int, @GroupIdInserted int
				select @GroupIdInserted = Id from inserted

				DECLARE Group_must_policy_cursor CURSOR FOR 
				SELECT [Id]
				FROM [Module]
				OPEN Group_must_policy_cursor
				FETCH NEXT FROM Group_must_policy_cursor 
				INTO @ModuleId

				WHILE @@FETCH_STATUS = 0
				BEGIN
					IF NOT EXISTS (	   SELECT *
							   FROM [Policy] a INNER JOIN [Group] b ON a.GroupId = b.Id    
							   WHERE  a.[ModuleId] =  @ModuleId AND a.GroupId =  @GroupIdInserted    
						  )
					 INSERT INTO [Policy]([GroupId],[ModuleId],[Val],[Created_Date],flag) values(@GroupIdInserted,@ModuleId,'',getdate(),'')
					FETCH NEXT FROM Group_must_policy_cursor INTO @ModuleId

				END 
				CLOSE Group_must_policy_cursor;
				DEALLOCATE Group_must_policy_cursor;
		COMMIT TRANSACTION Group_must_policy;
 		END TRY
		BEGIN CATCH
			PRINT 'rollback Group_must_policy';
			ROLLBACK TRAN Group_must_policy;	
		END CATCH

GO


-- default value

INSERT INTO [dbo].[User]([Status],[LoginName],[Email],[Avatar],[Birthday],[Sex],[FullName],[Password],[Address],[Phone],[Mobile],[Yahoo],[Skype],[Facebook],[Detail],[Skin],[JustLogin],[LastLogin],[flag]) VALUES(	'anonymous',	'anonymous',	'anonymous',	'anonymous',	GETDATE(),	'anonymous',	'anonymous',	'anonymous',	'anonymous',	'anonymous',	'anonymous',	'anonymous',	'anonymous',	'anonymous',	'anonymous',	'anonymous',		GETDATE(),	GETDATE(),	'')
GO




